﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace FirmaTransportowa
{
    public class WorkerLicence: IModel<WorkerLicence>
    {
        [System.ComponentModel.DisplayName("ID kierowcy")]
        public int driver_id { get; set; }
        [System.ComponentModel.DisplayName("ID prawa jazdy")]
        public int licence_id { get; set; }

        public WorkerLicence(int driver_id, int licence_id)
        {
            this.driver_id = driver_id;
            this.licence_id = licence_id;
        }

        public WorkerLicence()
        {

        }

        public static List<WorkerLicence> getLicences(int id) {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            List<WorkerLicence> list = new List<WorkerLicence>();

            try
            {
                if (id < 0)
                {
                    throw new ArgumentException();
                }
                conn.Open();

                SqlCommand cmd = new SqlCommand("Select * from Worker_Licence WHERE driver_ID = '" + id + "'", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        WorkerLicence workerLicence = new WorkerLicence();
                        workerLicence.driver_id = int.Parse(reader["driver_ID"].ToString());
                        workerLicence.licence_id = int.Parse(reader["licence_ID"].ToString());
                        list.Add(workerLicence);
                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }

        private Boolean ifExists(int driver_id, int licence_id) { 
            List<WorkerLicence> list = getLicences(driver_id);
            foreach (WorkerLicence licence in list) {
                if (licence.licence_id == licence_id) return true;
            }
            return false;
        }

        public int save() 
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            int createdId = -1;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();

                if (!ifExists(this.driver_id, this.licence_id))
                {
                    cmd.CommandText = "INSERT INTO Worker_Licence(driver_ID,licence_ID) VALUES(@driver_id, @licence_id) SELECT CONVERT(int, SCOPE_IDENTITY())";
                    cmd.Parameters.AddWithValue("@driver_id", this.driver_id);
                    cmd.Parameters.AddWithValue("@licence_id", this.licence_id);
                    cmd.Connection = conn;

                    createdId = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception e)
            {
                System.Console.Write(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return createdId;
        }

        public void delete() {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                conn.BeginTransaction();

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "DELETE FROM Worker_Licence WHERE driver_ID=@driver_id AND licence_ID=@licence_id";
                cmd.Parameters.AddWithValue("@driver_id", this.driver_id);
                cmd.Parameters.AddWithValue("@licence_id", this.licence_id);

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }
        }

        public static void deleteRecordsByWorkerID(Worker worker)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                conn.BeginTransaction();

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "DELETE FROM Worker_Licence WHERE driver_ID=@worker_id";
                cmd.Parameters.AddWithValue("@driver_id", worker.id);

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
